package com.vote.dao;

import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;

import com.vote.domain.VoteQuestionnaire;

@DAO(catalog = "vote")
public interface VoteQuestionnaireDAO {
	
	public static String TABLE=" vote_questionnaire ";
	
	public static String INSERT_FIELD=" questionnaire_name,questionnaire_description,questionnaire_time,questionnaire_count," +
			"questionnaire_group,questionnaire_status,questionnaire_type,user_id ";
	public static String SELECT_FIELD=INSERT_FIELD+",questionnaire_id ";
	
	/**
	 * 获得热门问卷
	 * @param offset
	 * @param length
	 * @return
	 */
	@SQL("select "+SELECT_FIELD+" from "+TABLE+" where questionnaire_status=1" +
			" order by questionnaire_count desc limit :offset,:length")
		public List<VoteQuestionnaire> getHotOnLineQuestionnaire(@SQLParam("offset")int offset,@SQLParam("length") int length);
	
	@SQL("select count(*) from "+TABLE+" where questionnaire_status=1")
	    public int getHotOnlineQuestionnaireCount();
	
	/**
	 * 获取最新问卷
	 * @param offset
	 * @param length
	 * @return
	 */
      @SQL("select "+SELECT_FIELD+" from "+TABLE+" where questionnaire_status=1" +			
			" order by questionnaire_time desc limit :offset,:length")
             public List<VoteQuestionnaire>getLatestOnlineQuestionnaire(@SQLParam("offset")int offset,@SQLParam("length") int length);
     
      @SQL("select count(*) from "+TABLE+" where questionnaire_status=1")
	    public int getLatestOnlineQuestionnaireCount();
	
      /**
	 * 根据用户信息获得该用户的所有问卷
	 * @param id
	 * @return
	 */
	  @SQL("select "+SELECT_FIELD+" from "+TABLE+" where user_id=:id and questionnaire_status <> 2" +
	            " order by questionnaire_time limit :offset,:length")
			public List<VoteQuestionnaire> getQuestionnaireListByUserId(@SQLParam("id")int id, @SQLParam("offset")int offset, @SQLParam("length")int length);
	
	  @SQL("select count(*) from "+TABLE+" where user_id=:id and questionnaire_status <> 2")
	      public int getQuestionnaireCountByUserId(@SQLParam("id") int id);
	
	  /**
	 * 根据type获得该类型的在线问卷
	 * @param type
	 * @return
	 */
	@SQL("select "+SELECT_FIELD+" from "+TABLE+" where questionnaire_type=:type and questionnaire_status=1" +
			" order by questionnaire_time limit :offset,:length")
			public List<VoteQuestionnaire> getQuestionnaireListByType(@SQLParam("type")int type, @SQLParam("offset")int offset, @SQLParam("length")int length);
	
	@SQL("select count(*) from "+TABLE+" where questionnaire_type=:type and questionnaire_status=1")
	    public int getQusetionnaireCountByType(@SQLParam("type")int type);
	
	/**
	 * 根据group获得该类型的在线问卷
	 * @param group
	 * @return
	 */
	@SQL("select "+SELECT_FIELD+" from "+TABLE+" where questionnaire_group=:group and questionnaire_status=1" +
	" order by questionnaire_time limit :offset,:length")
	public List<VoteQuestionnaire> getQuestionnaireListByGroup(@SQLParam("group")int group, @SQLParam("offset")int offset, @SQLParam("length")int length);
	
	@SQL("select count(*) from "+TABLE+" where questionnaire_group=:group and questionnaire_status=1")
    public int getQuestionnaireCountByGroup(@SQLParam("group")int group);
	
	/**
	 * 插入问卷信息
	 * @param votequestionnaire
	 */
	
	@SQL("insert into vote_questionnaire(" +INSERT_FIELD+")values(:model.questionnaireName,:model.questionnaireDescription,:model.questionnaireTime," +
			":model.questionnaireCount,:model.questionnaireGroup,:model.questionnaireStatus,:model.questionnaireType,:model.userId)")
			public void insertQuestionnaire(@SQLParam("model") VoteQuestionnaire votequestionnaire);
			
	/**
	 * 根据id删除该id的问卷
	 * @param id
	 */
	@SQL("delete from "+TABLE+" where questionnaire_id=:id")
	   public void deleteQuestionnaireById(@SQLParam("id") int id);
	
	/**
	 * 根据id获得该id的问卷
	 * @param id
	 * @return
	 */
	@SQL("select "+SELECT_FIELD+" from "+TABLE+" where questionnaire_id=:id")
	  public VoteQuestionnaire getQuestionnaireById(@SQLParam("id") int id);
	
	/**
	 * 更新状态
	 * @param id
	 * @param status
	 */
	@SQL("update "+TABLE+" set questionnaire_status=:status where questionnaire_id=:id")
	  public void updateQuestionnaireStatus(@SQLParam("id") int id,@SQLParam("status") int status);
	
	/**
	 * 获取刚插入questionnaire的ID
	 * @return
	 */
	@SQL("select LAST_INSERT_ID()")
	public int getLastInsertId();
	
	/**
	 * 更新问卷count。如果该问卷被回答，count加1
	 * @param id
	 */
	@SQL("update "+TABLE+" set questionnaire_count=questionnaire_count+1 where questionnaire_id=:id")
	public void addCount(@SQLParam("id") int id);
}
